CREATE TABLE [dbo].[DocumentMain]
(
[DocumentKey] [uniqueidentifier] NOT NULL CONSTRAINT [DF_DocumentMain_DocumentKey] DEFAULT (newid()),
[IsSystem] [bit] NOT NULL CONSTRAINT [DF_DocumentMain_IsSystem] DEFAULT ((0)),
[Blob] [image] NULL,
[DefaultChildAccessKey] [uniqueidentifier] NULL,
[UpdatedByUserKey] [uniqueidentifier] NOT NULL,
[UpdatedOn] [datetime] NOT NULL CONSTRAINT [DF_DocumentMain_UpdatedOn] DEFAULT (getdate()),
[LockedByUserKey] [uniqueidentifier] NULL,
[LockedOn] [datetime] NULL,
[AccessKey] [uniqueidentifier] NOT NULL,
[CreatedByUserKey] [uniqueidentifier] NOT NULL,
[CreatedOn] [datetime] NOT NULL,
[ContainsChildrenFlag] [bit] NOT NULL,
[DocumentVersionKey] [uniqueidentifier] NOT NULL,
[DocumentTypeCode] [nvarchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocumentStatusCode] [int] NOT NULL,
[DocumentName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[DocumentDescription] [nvarchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AlternateName] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[StatusUpdatedOn] [datetime] NOT NULL,
[StatusUpdatedByUserKey] [uniqueidentifier] NOT NULL,
[BranchedFromDocumentKey] [uniqueidentifier] NULL,
[RelatedDocumentVersionKey] [uniqueidentifier] NULL,
[VersionComment] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[VersionLabel] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MarkedForDeleteOn] [datetime] NULL,
[DocumentCode] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SystemKey] [uniqueidentifier] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
CREATE TRIGGER [dbo].[asi_DocumentMain_Update]
    ON [dbo].[DocumentMain]
    FOR UPDATE
AS
   UPDATE ComponentRegistry
      SET Name = inserted.DocumentName,
          AssemblyName = REPLACE(inserted.DocumentName, ' ', '') + 'Rules'
     FROM ComponentRegistry INNER JOIN deleted
       ON ComponentRegistry.Name = deleted.DocumentName AND ComponentRegistry.TypeName = 'Asi.Business.BusinessItem'
          INNER JOIN inserted ON inserted.DocumentKey = deleted.DocumentKey
    WHERE inserted.DocumentTypeCode = 'BOD'

GO
ALTER TABLE [dbo].[DocumentMain] ADD CONSTRAINT [PK_DocumentMain] PRIMARY KEY CLUSTERED ([DocumentKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DocumentMain_CreatedByUserKey] ON [dbo].[DocumentMain] ([CreatedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DocumentMain_DefaultChildAccessKey] ON [dbo].[DocumentMain] ([DefaultChildAccessKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DocumentMain_DocumentVersionKey_CreatedOn_AccessKey] ON [dbo].[DocumentMain] ([DocumentVersionKey], [CreatedOn] DESC, [AccessKey]) INCLUDE ([DocumentKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DocumentMain_DocumentName_DocumentVersionKey] ON [dbo].[DocumentMain] ([DocumentName], [DocumentVersionKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DocumentMain_DocumentVersionKey_DocumentKey_AccessKey_CreatedOn] ON [dbo].[DocumentMain] ([DocumentVersionKey], [DocumentKey], [AccessKey], [CreatedOn]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DocumentMain_DocumentVersionKey_DocumentName] ON [dbo].[DocumentMain] ([DocumentVersionKey], [DocumentName]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DocumentMain_DocumentVersionKey_DocumentStatusCode] ON [dbo].[DocumentMain] ([DocumentVersionKey], [DocumentStatusCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DocumentMain_LockedByUserKey] ON [dbo].[DocumentMain] ([LockedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DocumentMain_RelatedDocumentVersionKey] ON [dbo].[DocumentMain] ([RelatedDocumentVersionKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DocumentMain_StatusKey_VersionKey_Key_Name_Typecode] ON [dbo].[DocumentMain] ([DocumentStatusCode], [DocumentVersionKey], [DocumentKey], [DocumentName], [DocumentTypeCode]) INCLUDE ([RelatedDocumentVersionKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DocumentMain_StatusUpdatedByUserKey] ON [dbo].[DocumentMain] ([StatusUpdatedByUserKey]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_DocumentMain_UpdatedByUserKey] ON [dbo].[DocumentMain] ([UpdatedByUserKey]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[DocumentMain] ADD CONSTRAINT [FK_DocumentMain_AccessMain] FOREIGN KEY ([AccessKey]) REFERENCES [dbo].[AccessMain] ([AccessKey])
GO
ALTER TABLE [dbo].[DocumentMain] ADD CONSTRAINT [FK_DocumentMain_AccessMain_DefaultChild] FOREIGN KEY ([DefaultChildAccessKey]) REFERENCES [dbo].[AccessMain] ([AccessKey])
GO
ALTER TABLE [dbo].[DocumentMain] ADD CONSTRAINT [FK_DocumentMain_DocumentStatusRef] FOREIGN KEY ([DocumentStatusCode]) REFERENCES [dbo].[DocumentStatusRef] ([DocumentStatusCode])
GO
ALTER TABLE [dbo].[DocumentMain] ADD CONSTRAINT [FK_DocumentMain_DocumentTypeRef] FOREIGN KEY ([DocumentTypeCode]) REFERENCES [dbo].[DocumentTypeRef] ([DocumentTypeCode])
GO
ALTER TABLE [dbo].[DocumentMain] ADD CONSTRAINT [FK_DocumentMain_UniformRegistry] FOREIGN KEY ([DocumentKey]) REFERENCES [dbo].[UniformRegistry] ([UniformKey])
GO
ALTER TABLE [dbo].[DocumentMain] ADD CONSTRAINT [FK_DocumentMain_UniformRegistry_DocumentVersionKey] FOREIGN KEY ([DocumentVersionKey]) REFERENCES [dbo].[UniformRegistry] ([UniformKey])
GO
ALTER TABLE [dbo].[DocumentMain] ADD CONSTRAINT [FK_DocumentMain_UserMain_CreatedBy] FOREIGN KEY ([CreatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[DocumentMain] ADD CONSTRAINT [FK_DocumentMain_UserMain_LockedBy] FOREIGN KEY ([LockedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[DocumentMain] ADD CONSTRAINT [FK_DocumentMain_UserMain_StatusUpdatedBy] FOREIGN KEY ([StatusUpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO
ALTER TABLE [dbo].[DocumentMain] ADD CONSTRAINT [FK_DocumentMain_UserMain_UpdatedBy] FOREIGN KEY ([UpdatedByUserKey]) REFERENCES [dbo].[UserMain] ([UserKey])
GO